import DbServer.DbDomServer as Dds
from OutPut.outPut import op


class DbProjectServer:
    
    dbPath = r"C:\project\pythonProject\NGCBot_DXZ\Config\project.db"

    def __init__(self):
        pass

    def searchProject(self):
        """
        查询需要推送的项目信息
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute('SELECT * FROM project WHERE is_push=?', ('0',))
            result = cursor.fetchall()
            op(f'[-]: 查询项目信息: {result}')
            Dds.closeDb(conn, cursor)
            if result:
                columns = [column[0] for column in cursor.description]
                info_dicts = [dict(zip(columns, row)) for row in result]
                return info_dicts
            return False
        except Exception as e:
            op(f'[-]: 查询项目信息出现错误, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def searchProjectAndUser(self):
        """
        查询需要推送的项目信息，带负责人
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute(
                'SELECT * FROM   project as a  left join  district_user  as b on a.district = b.district_name  where a.is_push =?',
                ('0',))
            result = cursor.fetchall()
            op(f'[-]: 查询项目信息: {result}')
            Dds.closeDb(conn, cursor)
            if result:
                columns = [column[0] for column in cursor.description]
                info_dicts = [dict(zip(columns, row)) for row in result]
                return info_dicts
            return False
        except Exception as e:
            op(f'[-]: 查询项目信息出现错误, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def updateProjectPush(self, project_id, is_push):
        """
        更新工程推送状态
        :param ghId:
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute('UPDATE  project set  is_push =? WHERE project_id=?', (is_push, project_id))
            conn.commit()
            Dds.closeDb(conn, cursor)
            return True
        except Exception as e:
            op(f'[-]: 更新项目信息出现错误, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def searchProjectLostAndUser(self):
        """
        查询需要推送的项目信息，带负责人
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute(
                'SELECT * FROM   lost_project as a  left join  district_user  as b on a.district = b.district_name  where a.is_push =?',
                ('0',))
            result = cursor.fetchall()
            op(f'[-]: 查询项目信息: {result}')
            Dds.closeDb(conn, cursor)
            if result:
                columns = [column[0] for column in cursor.description]
                info_dicts = [dict(zip(columns, row)) for row in result]
                return info_dicts
            return False
        except Exception as e:
            op(f'[-]: 查询项目信息出现错误, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def updateProjectLostPush(self, project_name, is_push):
        """
        更新工程推送状态
        :param ghId:
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute('UPDATE  lost_project set  is_push =? WHERE project_name=?', (is_push, project_name))
            conn.commit()
            Dds.closeDb(conn, cursor)
            return True
        except Exception as e:
            op(f'[-]: 更新项目信息出现错误, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def insertProjectMore(self, data):
        """
        批量插入项目信息数据
        :data:批量数据集  [('2006-03-28', 'BUY', 'IBM', 1000, 45.00)]
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.executemany(
                'INSERT OR IGNORE INTO  project VALUES (?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?,?)',
                data)
            conn.commit()
            Dds.closeDb(conn, cursor)
            return True
        except Exception as e:
            op(f'[-]: 批量插入项目信息数据失败, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def insertProjectLost(self, data):
        """
        批量插入项目信息数据
        :data:批量数据集  [('2006-03-28', 'BUY', 'IBM', 1000, 45.00)]
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.executemany(
                'INSERT OR IGNORE INTO  lost_project(city,district,customer_name,project_name,industry,win_com,is_bom,is_gov,project_cota,project_link,win_time,create_time,is_push) VALUES (?, ?, ?,?,?, ?, ?,?,?, ?, ?,?,?)',
                data)
            conn.commit()
            Dds.closeDb(conn, cursor)
            return True
        except Exception as e:
            op(f'[-]: 批量插入项目信息数据失败, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def inserPushMessage(self, project_id, project_name, projectMsg, push_id, projectPushRoom, push_time):
        """
        插入推送数据记录
        :project_id:
        :projectMsg
        :push_id
        :projectPushRoom
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute(
                'INSERT INTO push_reply   (project_id, project_name,push_content, push_user,push_room,push_time,is_reply) VALUES (?,?,?,?,?,?,?);',
                (project_id, project_name, projectMsg, push_id, projectPushRoom, push_time, '0'))
            conn.commit()
            Dds.closeDb(conn, cursor)
            return True
        except Exception as e:
            op(f'[-]: 插入推送数据记录错误: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def inserPushMessageLost(self, project_name, projectMsg, push_id, projectPushRoom, push_time):
        """
        插入推送数据记录
        :project_id:
        :projectMsg
        :push_id
        :projectPushRoom
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute(
                'INSERT INTO push_reply_lost   (project_name,push_content, push_user,push_room,push_time,is_reply) VALUES (?,?,?,?,?,?);',
                (project_name, projectMsg, push_id, projectPushRoom, push_time, '0'))
            conn.commit()
            Dds.closeDb(conn, cursor)
            return True
        except Exception as e:
            op(f'[-]: 插入推送数据记录错误: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def updatePushMsgByContent(self, reply_content, reply_user, reply_time, project_name):
        """
        更新回复数据
        :param ghId:
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute(
                'UPDATE  push_reply  set reply_content=?,reply_user=?,reply_time=?,is_reply=? WHERE project_name=?',
                (reply_content, reply_user, reply_time, '1', project_name))
            conn.commit()
            Dds.closeDb(conn, cursor)
            return True
        except Exception as e:
            op(f'[-]: 更新回复数据, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def searchProjectByOpentime(self, project_open_time):
        """
        查询需要推送的项目信息，带负责人
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute(
                'SELECT * FROM   project as a  left join  district_user  as b on a.district = b.district_name  where a.project_open_time =?',
                (project_open_time,))
            result = cursor.fetchall()
            op(f'[-]: 查询项目信息: {result}')
            Dds.closeDb(conn, cursor)
            if result:
                columns = [column[0] for column in cursor.description]
                info_dicts = [dict(zip(columns, row)) for row in result]
                return info_dicts
            return False
        except Exception as e:
            op(f'[-]: 查询项目信息出现错误, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return False

    def showDistrictUser(self, ):
        """
        查询县区管理者
        :return:
        """
        conn, cursor = Dds.openDb(self.dbPath)
        dataDict = dict()
        try:
            cursor.execute('SELECT wx_id as wxId, wx_name as wxName FROM district_user')
            result = cursor.fetchall()
            Dds.closeDb(conn, cursor)
            if result:
                for res in result:
                    dataDict[res[0]] = res[1]
            return dataDict
        except Exception as e:
            op(f'[-]: 查询县区管理者错误, 错误信息: {e}')
            Dds.closeDb(conn, cursor)
            return dataDict

    def searchDayPushInfo(self, time1, time2):
        """
        查询推送日报
        :return:
        """
        my_dict = {}
        conn, cursor = Dds.openDb(self.dbPath)
        try:
            cursor.execute(
                'select count(1) as count from push_reply as a  left join project as b on a.project_id = b.project_id  where push_time >?  and push_time <?',
                (time1, time2))
            resultAll = cursor.fetchall()
            op(f'[-]: 查询今天推送数量: {resultAll}')
            if resultAll:
                columns = [column[0] for column in cursor.description]
                all_dicts = [dict(zip(columns, row)) for row in resultAll]
                my_dict["all_dicts"] = all_dicts

            cursor.execute(
                'select count(1) as count from push_reply as a  left join project as b on a.project_id = b.project_id  where is_reply = ? and push_time >?  and push_time<?',
                ('0', time1, time2))
            resultNoPush = cursor.fetchall()
            op(f'[-]: 查询今天未回复数量: {resultNoPush}')
            if resultNoPush:
                columns = [column[0] for column in cursor.description]
                no_push_dicts = [dict(zip(columns, row)) for row in resultNoPush]
                my_dict["no_push_dicts"] = no_push_dicts

            cursor.execute(
                'select district,count(1) as count from push_reply as a  left join project as b on a.project_id = b.project_id  where is_reply = ? and push_time >?  and push_time<? GROUP BY(district)',
                ('0', time1, time2))
            resultDic = cursor.fetchall()
            op(f'[-]: 查询今天县区未回复数量: {resultDic}')
            if resultDic:
                columns = [column[0] for column in cursor.description]
                dic_dicts = [dict(zip(columns, row)) for row in resultDic]
                my_dict["dic_dicts"] = dic_dicts

            cursor.execute(
                'select district,b.project_name  from push_reply as a  left join project as b on a.project_id = b.project_id  where is_reply = ? and push_time >?  and push_time<?',
                ('0', time1, time2))
            resultProject = cursor.fetchall()
            op(f'[-]: 查询今天未回复数据: {resultDic}')
            if resultDic:
                columns = [column[0] for column in cursor.description]
                pro_dicts = [dict(zip(columns, row)) for row in resultProject]
                my_dict["pro_dicts"] = pro_dicts
            Dds.closeDb(conn, cursor)
            return my_dict
        except Exception as e:
            op(f'[-]: 查询推送日报错误: {e}')
            Dds.closeDb(conn, cursor)
            return False
